package com.advertisementsystem.Mapper;

import com.advertisementsystem.Dto.*;
import com.advertisementsystem.entity.Schedule;
import com.advertisementsystem.vo.AdEffect;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Mapper
public interface AdScheduleMapper extends BaseMapper<Schedule> {


    @Select("SELECT * FROM ad_schedule WHERE device_id = #{deviceId} " +
            "AND ((start_time BETWEEN #{startDate} AND #{endDate}) " +
            "OR (end_time BETWEEN #{startDate} AND #{endDate}))")
    List<Schedule> findByDeviceIdAndDateRange(
            @Param("deviceId") Long deviceId,
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate);

    @Select("SELECT * FROM ad_schedule WHERE advertisement_id = #{adId}")
    List<Schedule> findByAdvertisementId(@Param("adId") Long adId);



    @Select("SELECT SUM(impressions) AS impressions, " +
            "SUM(clicks) AS clicks, " +
            "ROUND(SUM(clicks)/SUM(impressions)*100, 2) AS ctr " +
            "FROM ad_schedule " +
            "WHERE advertisement_id IN " +
            "(SELECT id FROM advertisement WHERE advertiser_id = #{advertiserId})")
    AdEffect sumAdEffects(@Param("advertiserId") Long advertiserId);

    @Select("SELECT SUM(impressions) FROM ad_schedule " +
            "WHERE advertisement_id IN " +
            "(SELECT id FROM advertisement WHERE advertiser_id = #{advertiserId})")
    Long sumImpressions(@Param("advertiserId") Long advertiserId);

    @Select("SELECT SUM(clicks) FROM ad_schedule " +
            "WHERE advertisement_id IN " +
            "(SELECT id FROM advertisement WHERE advertiser_id = #{advertiserId})")
    Long sumClicks(@Param("advertiserId") Long advertiserId);




    @Select("SELECT " +
            "COALESCE(SUM(s.view_count), 0) AS totalImpressions, " +
            "COALESCE(SUM(s.play_count), 0) AS totalClicks, " +
            "ROUND(IFNULL(SUM(s.view_count)/NULLIF(SUM(s.play_count), 0)*100, 0), 2) AS avgCTR, " +
            "COALESCE(SUM(s.view_count * s.play_count/1000), 0) AS totalCost " +
            "FROM statistics s " +
            "JOIN advertisement a ON s.ad_id = a.id " +
            "WHERE a.advertiser_id = #{advertiserId} " +
            // "AND s.start_time >= #{start} AND s.end_time <= #{end} " +
            "AND s.date >= #{start} AND s.date <= #{end}")
    MetricsDTO selectMetrics(@Param("advertiserId") Long advertiserId,
                             @Param("start") Date start,
                             @Param("end") Date end);



    @Select("SELECT s.date AS date, " +
            "SUM(s.play_count) AS impressions, " +
            "SUM(s.view_count) AS clicks " +
            "FROM statistics s " +
            "JOIN advertisement a ON s.ad_id = a.id " +
            "WHERE a.advertiser_id = #{advertiserId} " +
            "AND s.date >= #{start} AND s.date <= #{end} " +
            "GROUP BY s.date " +
            "ORDER BY s.date")
    List<DailyStatDTO> selectTrend(@Param("advertiserId") Long advertiserId,
                                   @Param("start") Date start,
                                   @Param("end") Date end);

    @Select("SELECT a.title, SUM(s.view_count) AS impressions " +
            "FROM statistics s " +
            "JOIN advertisement a ON s.ad_id = a.id " +
            "WHERE a.advertiser_id = #{advertiserId} " +
            "AND s.date >= #{start} AND s.date <= #{end} " +
            "GROUP BY a.id, a.title " +
            "ORDER BY SUM(s.play_count) DESC LIMIT 10")
    List<TopAdDTO> selectTopAds(@Param("advertiserId") Long advertiserId,
                                @Param("start") Date start,
                                @Param("end") Date end);

    @Select("SELECT CASE " +
            "WHEN ctr < 1 THEN '0-1%' " +
            "WHEN ctr < 2 THEN '1-2%' " +
            "WHEN ctr < 3 THEN '2-3%' " +
            "ELSE '3%以上' END AS ctrRange, " +
            "COUNT(*) AS count " +
            "FROM (SELECT a.id, (SUM(s.view_count)/NULLIF(SUM(s.play_count), 0))*100 AS ctr " +
            "FROM statistics s " +
            "JOIN advertisement a ON s.ad_id = a.id " +
            "WHERE a.advertiser_id = #{advertiserId} " +
            "AND s.date >= #{start} AND s.date <= #{end} " +
            "GROUP BY a.id " +
            "HAVING SUM(s.play_count) > 0) t " +
            "GROUP BY ctrRange " +
            "ORDER BY CASE ctrRange " +
            "WHEN '0-1%' THEN 1 " +
            "WHEN '1-2%' THEN 2 " +
            "WHEN '2-3%' THEN 3 " +
            "ELSE 4 END")
    List<CtrDistributionDTO> selectCtrDistribution(@Param("advertiserId") Long advertiserId,
                                                   @Param("start") Date start,
                                                   @Param("end") Date end);


}
